package com.dongway.netcondition.service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dongway.core.common.Paginator;
import com.dongway.core.common.ValidationException;
import com.dongway.core.service.BaseService;
import com.dongway.core.utils.JdbcUtils;
import com.dongway.netcondition.criteria.DevCriteria;
import com.dongway.netcondition.dto.DevStateDTO;
import com.dongway.netcondition.model.DevNetState;

@Service
public class DevNetStateService extends BaseService {

    public List<DevStateDTO> all(Paginator paginator) {
        String sql =
                "select t.* ,tb.id as branchId, tb.name as branchName "
                        + ",tt.name as tollStationName ,tr.name as roadName "
                        + ", tr.id as roadId from t_dev_net_state t "
                        + ",t_region_branch tb ,t_toll_station tt ,t_road tr "
                        + "where tt.road_id = tr.id and "
                        + "tr.branch_id = tb.id and t.toll_station_id = tt.id "
                        + "order by tb.id, tr.id, tt.id";
        return getDao().query(DevStateDTO.class, sql, paginator);
    }


    public List<DevStateDTO> allOrderByDevId(Paginator paginator) {
        String sql =
                "select t.* ,tb.id as branchId, tb.name as branchName "
                        + ",tt.name as tollStationName ,tr.name as roadName "
                        + ", tr.id as roadId from t_dev_net_state t "
                        + ",t_region_branch tb ,t_toll_station tt ,t_road tr "
                        + "where tt.road_id = tr.id and "
                        + "tr.branch_id = tb.id and t.toll_station_id = tt.id "
                        + "order by t.id";
        return getDao().query(DevStateDTO.class, sql, paginator);
    }


    public List<DevStateDTO> getDevStatesByBranchId(Integer branchId,
            Paginator paginator) {
        String sql =
                "select t.* ,tb.id as branchId, tb.name as branchName "
                        + ",tt.name as tollStationName ,tr.name as roadName "
                        + ", tr.id as roadId from t_dev_net_state t "
                        + ",t_region_branch tb ,t_toll_station tt ,t_road tr "
                        + "where tb.id = ? and tt.road_id = tr.id "
                        + "and tr.branch_id = tb.id and "
                        + "t.toll_station_id = tt.id "
                        + "order by tb.id, tr.id, tt.id";
        return getDao().query(DevStateDTO.class, sql, paginator, branchId);
    }


    public List<DevNetState> search(DevCriteria criteria, Paginator paginator) {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuilder sql =
                new StringBuilder("select t.* from t_dev_net_state t ");
        if (null != criteria.getBranchId()) {
            sql.insert(9, " ,tb.name as branchName");
            sql.append(",t_branch tb ");
        }
        if (null != criteria.getTollStationId()) {
            sql.insert(9, " ,tt.name as tollStationName");
            sql.append(",t_toll_station tt ");
        }
        if (null != criteria.getRoadId()) {
            sql.insert(9, " ,tr.name as roadName");
            sql.append(",t_road tr ");
        }
        if (null != criteria.getRoadId()) {
            JdbcUtils.appendWhereOrAnd(sql);
            sql.append("t.road_id = tr.id and t.road_id = :roadId");
            map.put("roadId", criteria.getRoadId());
        }
        if (null != criteria.getBranchId()) {
            JdbcUtils.appendWhereOrAnd(sql);
            sql.append("t.branch_id = tb.id and t.branch_id = :branchId");
            map.put("branchId", criteria.getBranchId());
        }
        if (null != criteria.getTollStationId()) {
            JdbcUtils.appendWhereOrAnd(sql);
            sql.append("t.toll_station_id = tt.id and"
                    + " t.toll_station_id = :tollStationId");
            map.put("tollStationId", criteria.getTollStationId());
        }
        if (null != criteria.getDevId()) {
            JdbcUtils.appendWhereOrAnd(sql);
            sql.append("t.id = :devId");
            map.put("devId", criteria.getDevId());
        }
        if (!StringUtils.isEmpty(criteria.getDevName())) {
            JdbcUtils.appendWhereOrAnd(sql);
            sql.append("t.name = :devName");
            map.put("devName", criteria.getDevName());
        }
        return getDao()
            .query(DevNetState.class, sql.toString(), map, paginator);
    }


    public List<DevNetState> search(Integer roadid) {
        String sql =
                "select t.* from t_dev_net_state t, t_toll_station tt "
                        + "where t.toll_station_id=tt.id and tt.road_id=?";
        return getDao().query(DevNetState.class, sql, roadid);
    }


    /**
     * save or update
     * 
     * @param devNetState
     */
    public void update(DevNetState devNetState) {
        if (null == devNetState.getId()) {
            throw new ValidationException("it's necessary for id.");
        }
        if (getDao().isExisted(DevNetState.class, devNetState.getId())) {
            getDao().updateIgnoreNull(devNetState);
        } else {
            getDao().save(devNetState);
        }
    }


    @Transactional
    public void batchUpdate(List<DevNetState> ds) {

        String sql = "update t_dev_net_state t, t_dev_info t1 set "
                + "t.toll_station_id=?, t1.toll_station_id=? where t.id=? and t1.id=?";
        getDao().batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i)
                    throws SQLException {
                DevNetState dto = ds.get(i);
                ps.setInt(1, dto.getTollStationId());
                ps.setInt(2, dto.getTollStationId());
                ps.setInt(3, dto.getId());
                ps.setInt(4, dto.getId());
            }


            @Override
            public int getBatchSize() {
                return ds.size();
            }
        });
    }
}
